package com.web22_2_1.service.impl;

import com.web22_2_1.entity.Student;
import com.web22_2_1.mapper.GradeSQL;
import com.web22_2_1.mapper.StudentSQL;
import com.web22_2_1.service.StudentService;
import com.web22_2_1.utils.DruidDemo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentServiceImpl implements StudentService {
    @Override
    public List<Student> getAllStudents() {
        List<Student> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pStatement = null;
        try {
            connection = DruidDemo.getConnection();
            pStatement = connection.prepareStatement(StudentSQL.SELECT_ALL_STUDENTS);
            ResultSet rs = pStatement.executeQuery();
            while(rs.next()){
                Student student = new Student();
                student.setStudentId(rs.getInt("StudentID"));
                student.setUserId(rs.getInt("UserID"));
                student.setName(rs.getString("Name"));
                student.setGender(rs.getString("Gender").charAt(0));
                student.setBirthDate(rs.getDate("BirthDate"));
                student.setAddress(rs.getString("Address"));
                student.setPhone(rs.getString("Phone"));
                student.setEmail(rs.getString("Email"));
                list.add(student);
            }
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DruidDemo.destroy(connection, pStatement, null);
        }
        return list;
    }

    @Override
    public Student getStudentById(int studentId) {
        Student student = null;
        Connection connection = null;
        PreparedStatement pStatement = null;
        try {
            connection = DruidDemo.getConnection();
            pStatement = connection.prepareStatement(StudentSQL.SELECT_STUDENT_BY_ID);
            pStatement.setInt(1,studentId);
            ResultSet rs = pStatement.executeQuery();
            while(rs.next()){
                student = new Student();
                student.setStudentId(rs.getInt("StudentID"));
                student.setUserId(rs.getInt("UserID"));
                student.setName(rs.getString("Name"));
                student.setGender(rs.getString("Gender").charAt(0));
                student.setBirthDate(rs.getDate("BirthDate"));
                student.setAddress(rs.getString("Address"));
                student.setPhone(rs.getString("Phone"));
                student.setEmail(rs.getString("Email"));
            }
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DruidDemo.destroy(connection, pStatement, null);
        }
        return student;
    }

    @Override
    public Student getStudentByName(String studentName) {
        Student student = null;
        Connection connection = null;
        PreparedStatement pStatement = null;
        try {
            connection = DruidDemo.getConnection();
            pStatement = connection.prepareStatement(StudentSQL.SELECT_STUDENT_BY_NAME);
            pStatement.setString(1,studentName);
            ResultSet rs = pStatement.executeQuery();
            while(rs.next()){
                student = new Student();
                student.setStudentId(rs.getInt("StudentID"));
                student.setUserId(rs.getInt("UserID"));
                student.setName(rs.getString("Name"));
                student.setGender(rs.getString("Gender").charAt(0));
                student.setBirthDate(rs.getDate("BirthDate"));
                student.setAddress(rs.getString("Address"));
                student.setPhone(rs.getString("Phone"));
                student.setEmail(rs.getString("Email"));
            }
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DruidDemo.destroy(connection, pStatement, null);
        }
        return student;
    }

    @Override
    public boolean addStudent(Student student) {
        Connection connection = null;
        PreparedStatement pStatement = null;
        int rows = 0;
        try {
            connection = DruidDemo.getConnection();
            pStatement = connection.prepareStatement(StudentSQL.INSERT_STUDENT);
            pStatement.setInt(1,student.getUserId());
            pStatement.setString(2,student.getName());
            pStatement.setString(3,String.valueOf(student.getGender()));
            pStatement.setDate(4,new java.sql.Date(student.getBirthDate().getTime()));
            pStatement.setString(5,student.getAddress());
            pStatement.setString(6,student.getPhone());
            pStatement.setString(7,student.getEmail());

            rows = pStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DruidDemo.destroy(connection, pStatement, null);
        }
        return rows>0;
    }
}
